package com.bringway.p2p.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.global.util.StringUtils;
import com.bringway.p2p.dao.P2PGrtzzqDao;

import net.sf.json.JSONObject;

/*
 * @author fan
 * @className P2PGrtzzqDaoImpl
 * @description 个人投资债权信息持久层dao实现类
 * @date 2016年1月4日下午12:57:18
 */
@Repository
public class P2PGrtzzqDaoImpl extends P2PJdbcDao implements P2PGrtzzqDao {

	Logger logger = Logger.getLogger(P2PGrtzzqDaoImpl.class);

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.bringway.p2p.dao.P2PGrtzzqDao#queryGrttzq(net.sf.json.JSONObject)
	 */
	@Override
	public List queryGrttzq(JSONObject parms) {
		String userId = parms.optString("userId");
		String TZSJ_START = parms.optString("TZSJ_START");
		String TZSJ_END = parms.optString("TZSJ_END");
		String TCZT = parms.optString("TCZT");
		String HKZT = parms.optString("HKZT");
		String SJHM = parms.optString("SJHM");
		String TZ_SOURCE = parms.optString("TZ_SOURCE");
		String XMMC = parms.optString("XMMC");
		String HTBH = parms.optString("HTBH");
		String TJR = parms.optString("TJR");
		int curpage = parms.optInt("curpage");
		int pageSize = parms.optInt("pageSize");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		sql.append("select  t2.SJHM,t3.USERID,t3.ZQID,t3.TCJE,t3.TZSJ,t3.TCZT,t3.HKZT,t3.JXRQ HKRQ,"
				+ " t3.TZSY,t3.TZ_SOURCE,t3.POS_CODE,t4.XMMC,t3.HKZT IS_JX,t3.HTBH,t4.XM_TYPE,"
				+ " t4.HK_TYPE,t4.QX,t4.QX_MONTH,t4.SJSYL,t4.SJSYL_SHOW,t4.JXRQ,t5.GYH OA_GYH,"
				+ " t5.DQ,t5.BM,t5.SS,t5.ZW,t5.ZJ,t5.XM OA_XM,t5.SJHM  OA_SJHM, t6.TJR,t6.TJJB KHDJ "
				+ " from t_zhfw_tzrxx t3  left join t_zhfw_zqzrlb t4 on  t3.ZQID=t4.ID   "
				+ " left join  t_zhfw_tjr_tjjb t6 on t3.USERID = t6.USER_NAME   "
				+ " left join t_zhfw_user t2 on t2.USER_NAME=t6.USER_NAME and t2.DISABLED='1' "
				+ " left join t_zhfw_oa_user t5 on t5.GYH = t2.TJR ");
		sql.append(" where 1=1");
		if (StringUtils.isNotEmpty(userId)) {
			sql.append(" AND t3.USERID like " + "'%" + userId + "%'");
		}
		if (StringUtils.isNotEmpty(SJHM)) {
			sql.append(" AND t2.SJHM like '%" + SJHM + "%' ");
		}
		if (StringUtils.isNotEmpty(TZSJ_START)) {
			sql.append(" and t3.TZSJ >= :TZSJ_START ");
			parmsMap.put("TZSJ_START", TZSJ_START + " 00:00:00");
		}
		if (StringUtils.isNotEmpty(TZSJ_END)) {
			sql.append(" and t3.TZSJ <= :TZSJ_END ");
			parmsMap.put("TZSJ_END", TZSJ_END + " 23:59:59");
		}
		if (StringUtils.isNotEmpty(TCZT) && (TCZT.equals("00"))) {
			sql.append(" and t3.TCZT = '成功' ");
		}
		if (StringUtils.isNotEmpty(TCZT) && (TCZT.equals("01"))) {
			sql.append(" and t3.TCZT = '待审核' ");
		}
		if (StringUtils.isNotEmpty(TCZT) && (TCZT.equals("02"))) {
			sql.append(" and t3.TCZT = '失败' ");
		}
		if (StringUtils.isNotEmpty(TCZT) && (TCZT.equals("03"))) {
			sql.append(" and t3.TCZT = '转让成功' ");
		}
		if (StringUtils.isNotEmpty(HKZT)) {
			sql.append(" and t3.HKZT = :HKZT ");
			parmsMap.put("HKZT", HKZT);
		}
		if (StringUtils.isNotEmpty(TZ_SOURCE)) {
			sql.append(" and t3.TZ_SOURCE = :TZ_SOURCE ");
			parmsMap.put("TZ_SOURCE", TZ_SOURCE);
		}
		if (StringUtils.isNotEmpty(XMMC)) {
			sql.append(" AND t4.XMMC like '%" + XMMC + "%' ");
		}
		if (StringUtils.isNotEmpty(HTBH)) {
			sql.append(" AND t3.HTBH like '%" + HTBH + "%' ");
		}
		if (StringUtils.isNotEmpty(TJR)) {
			String[] tjrs = StringUtils.split(TJR, ",");
			String[] sqls = new String[tjrs.length];
			for (int i = 0; i < tjrs.length; i++) {
				sqls[i] = "t6.TJR like :tjr" + i;
				parmsMap.put("tjr" + i, tjrs[i] + "%");
			}
			sql.append(" AND (" + StringUtils.join(sqls, " OR ") + ")");
		}
		
		sql.append(" order by t3.TZSJ DESC ");
		if (0 != pageSize) {
			sql.append("  limit :curpage,:pageSize ");
			parmsMap.put("curpage", curpage);
			parmsMap.put("pageSize", pageSize);
		}
		logger.info("queryGrttzq sql is : " + sql.toString());
		logger.info("queryGrttzq parmsMap is : " + parmsMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), parmsMap);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.bringway.p2p.dao.P2PGrtzzqDao#getPageCount(net.sf.json.JSONObject)
	 */
	@SuppressWarnings("deprecation")
	@Override
	public int getPageCount(JSONObject parms) {
		String userId = parms.optString("userId");
		String TZSJ_START = parms.optString("TZSJ_START");
		String TZSJ_END = parms.optString("TZSJ_END");
		String TCZT = parms.optString("TCZT");
		String HKZT = parms.optString("HKZT");
		String SJHM = parms.optString("SJHM");
		String TZ_SOURCE = parms.optString("TZ_SOURCE");
		String XMMC = parms.optString("XMMC");
		String HTBH = parms.optString("HTBH");
		String TJR = parms.optString("TJR");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		sql.append("select count(*) from t_zhfw_tzrxx t3  left join t_zhfw_zqzrlb t4 on  t3.ZQID=t4.ID   "
				+ " left join  t_zhfw_tjr_tjjb t6 on t3.USERID = t6.USER_NAME   "
				+ " left join t_zhfw_user t2 on t2.USER_NAME=t6.USER_NAME and t2.DISABLED='1' "
				+ " left join t_zhfw_oa_user t5 on t5.GYH = t2.TJR ");
		sql.append(" where 1=1");
		if (StringUtils.isNotEmpty(userId)) {
			sql.append(" AND t3.USERID like " + "'%" + userId + "%'");
		}
		if (StringUtils.isNotEmpty(SJHM)) {
			sql.append(" AND t2.SJHM like '%" + SJHM + "%' ");
		}
		if (StringUtils.isNotEmpty(TZSJ_START)) {
			sql.append(" and t3.TZSJ >= :TZSJ_START ");
			parmsMap.put("TZSJ_START", TZSJ_START + " 00:00:00");
		}
		if (StringUtils.isNotEmpty(TZSJ_END)) {
			sql.append(" and t3.TZSJ <= :TZSJ_END ");
			parmsMap.put("TZSJ_END", TZSJ_END + " 23:59:59");
		}
		if (StringUtils.isNotEmpty(TCZT) && (TCZT.equals("00"))) {
			sql.append(" and t3.TCZT = '成功' ");
		}
		if (StringUtils.isNotEmpty(TCZT) && (TCZT.equals("01"))) {
			sql.append(" and t3.TCZT = '待审核' ");
		}
		if (StringUtils.isNotEmpty(TCZT) && (TCZT.equals("02"))) {
			sql.append(" and t3.TCZT = '失败' ");
		}
		if (StringUtils.isNotEmpty(TCZT) && (TCZT.equals("03"))) {
			sql.append(" and t3.TCZT = '转让成功' ");
		}
		if (StringUtils.isNotEmpty(HKZT)) {
			sql.append(" and t3.HKZT = :HKZT ");
			parmsMap.put("HKZT", HKZT);
		}
		if (StringUtils.isNotEmpty(TZ_SOURCE)) {
			sql.append(" and t3.TZ_SOURCE = :TZ_SOURCE ");
			parmsMap.put("TZ_SOURCE", TZ_SOURCE);
		}
		if (StringUtils.isNotEmpty(XMMC)) {
			sql.append(" AND t4.XMMC like '%" + XMMC + "%' ");
		}
		if (StringUtils.isNotEmpty(HTBH)) {
			sql.append(" AND t3.HTBH like '%" + HTBH + "%' ");
		}
		if (StringUtils.isNotEmpty(TJR)) {
			String[] tjrs = StringUtils.split(TJR, ",");
			String[] sqls = new String[tjrs.length];
			for (int i = 0; i < tjrs.length; i++) {
				sqls[i] = "t6.TJR like :tjr" + i;
				parmsMap.put("tjr" + i, tjrs[i] + "%");
			}
			sql.append(" AND (" + StringUtils.join(sqls, " OR ") + ")");
		}
		logger.info("getPageCount sql is : " + sql.toString());
		logger.info("getPageCount parmsMap is : " + parmsMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), parmsMap);
	}

	@SuppressWarnings("unchecked")
	@Override
	public float getGrtzJe(JSONObject parms) {
		String userId = parms.optString("userId");
		String TZSJ_START = parms.optString("TZSJ_START");
		String TZSJ_END = parms.optString("TZSJ_END");
		String TCZT = parms.optString("TCZT");
		String HKZT = parms.optString("HKZT");
		String SJHM = parms.optString("SJHM");
		String TZ_SOURCE = parms.optString("TZ_SOURCE");
		String XMMC = parms.optString("XMMC");
		String TJR = parms.optString("TJR");
		String HTBH = parms.optString("HTBH");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parmsMap = new HashMap<String, Object>();

		sql.append("select round(sum(t3.TCJE),2) S_TZJE from t_zhfw_tzrxx t3  left join t_zhfw_zqzrlb t4 on  t3.ZQID=t4.ID   "
				+ " left join  t_zhfw_tjr_tjjb t6 on t3.USERID = t6.USER_NAME   "
				+ " left join t_zhfw_user t2 on t2.USER_NAME=t6.USER_NAME and t2.DISABLED='1' "
				+ " left join t_zhfw_oa_user t5 on t5.GYH = t2.TJR ");
		sql.append(" where 1=1");
		if (StringUtils.isNotEmpty(userId)) {
			sql.append(" AND t3.USERID like " + "'%" + userId + "%'");
		}
		
		if (StringUtils.isNotEmpty(SJHM)) {
			sql.append(" AND t2.SJHM like '%" + SJHM + "%' ");
		}
		
		if (StringUtils.isNotEmpty(TZSJ_START)) {
			sql.append(" and t3.TZSJ >= :TZSJ_START ");
			parmsMap.put("TZSJ_START", TZSJ_START + " 00:00:00");
		}
	
		if (StringUtils.isNotEmpty(TZSJ_END)) {
			sql.append(" and t3.TZSJ <= :TZSJ_END ");
			parmsMap.put("TZSJ_END", TZSJ_END + " 23:59:59");
		}
		
		if (StringUtils.isNotEmpty(TCZT) && (TCZT.equals("00"))) {
			sql.append(" and t3.TCZT = '成功' ");
		}
		
		if (StringUtils.isNotEmpty(TCZT) && (TCZT.equals("01"))) {
			sql.append(" and t3.TCZT = '待审核' ");
		}
		
		if (StringUtils.isNotEmpty(TCZT) && (TCZT.equals("02"))) {
			sql.append(" and t3.TCZT = '失败' ");
		}
		
		if (StringUtils.isNotEmpty(TCZT) && (TCZT.equals("03"))) {
			sql.append(" and t3.TCZT = '转让成功' ");
		}
		
		if (StringUtils.isNotEmpty(HKZT)) {
			sql.append(" and t3.HKZT = :HKZT ");
			parmsMap.put("HKZT", HKZT);
		}
		
		if (StringUtils.isNotEmpty(TZ_SOURCE)) {
			sql.append(" and t3.TZ_SOURCE = :TZ_SOURCE ");
			parmsMap.put("TZ_SOURCE", TZ_SOURCE);
		}
		
		if (StringUtils.isNotEmpty(XMMC)) {
			sql.append(" AND t4.XMMC like '%" + XMMC + "%' ");
		}
		
		if (StringUtils.isNotEmpty(HTBH)) {
			sql.append(" AND t3.HTBH like '%" + HTBH + "%' ");
		}
		if (StringUtils.isNotEmpty(TJR)) {
			String[] tjrs = StringUtils.split(TJR, ",");
			String[] sqls = new String[tjrs.length];
			for (int i = 0; i < tjrs.length; i++) {
				sqls[i] = "t6.TJR like :tjr" + i;
				parmsMap.put("tjr" + i, tjrs[i] + "%");
			}
			sql.append(" AND (" + StringUtils.join(sqls, " OR ") + ")");
		}
		sql.append(" order by t3.TZSJ DESC ");
		logger.info("getGrtzJe sql is : " + sql.toString());
		logger.info("getGrtzJe parmsMap is : " + parmsMap.toString());
		return this.getNamedParameterJdbcTemplate().query(sql.toString(), parmsMap, new ResultSetExtractor() {

			@Override
			public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
				float sJe = 0;
				if (rs.next()) {
					sJe = rs.getFloat("S_TZJE");
				}
				return sJe;
			}
		});
	}

	@Override
	public List youjiaGrttzq(JSONObject parms) {
		String userId = parms.optString("userId");
		String TZSJ_START = parms.optString("TZSJ_START");
		String TZSJ_END = parms.optString("TZSJ_END");
		String TCZT = parms.optString("TCZT");
		String HKZT = parms.optString("HKZT");
		String SJHM = parms.optString("SJHM");
		String TZ_SOURCE = parms.optString("TZ_SOURCE");
		String XMMC = parms.optString("XMMC");
		String TJR = parms.optString("TJR");
		String Q_HTBH = parms.optString("Q_HTBH");
		String Q_DQ = parms.optString("Q_DQ");
		String Q_BM = parms.optString("Q_BM");
		String Q_SS = parms.optString("Q_SS");
		int curpage = parms.optInt("curpage");
		int pageSize = parms.optInt("pageSize");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		/*
		 * sql.append(
		 * "SELECT TEMP.ID, TEMP.USERID, tt1.SJHM, TEMP.ZQID, TEMP.TCJE, TEMP.TZSJ"
		 * ); sql.append(
		 * " ,TEMP.TCZT, TEMP.HKZT, TEMP.JXRQ HKRQ, TEMP.TZSY, TEMP.TZ_SOURCE, TEMP.POS_CODE"
		 * ); sql.append(
		 * " ,datediff(now(),temp.tzsj) IS_JX, TEMP.HTBH,tt2.SJSYL,tt2.SJSYL_SHOW"
		 * ); sql.append(
		 * " ,tt2.XMMC, tt2.JE, tt2.JD, tt2.QX, tt2.FBSJ, tt2.ZT, tt2.JKR, tt2.DFRID, tt2.MJQ, tt2.JXRQ, tt2.XM_TYPE, tt2.HK_TYPE, tt2.QX_MONTH"
		 * ); sql.append(
		 * " ,case when tt1.TJR is null or tt1.TJR ='' then case when TT7.TJR is null or  TT7.TJR ='' then null else TT7.TJR end else tt1.TJR end TJR "
		 * ); sql.append(
		 * " ,case when tt1.TJR is null or tt1.TJR ='' then case when TT7.TJR is null or  TT7.TJR ='' then null else '02' end else '01' end KHDJ "
		 * ); sql.append(
		 * " ,tt4.GYH OA_GYH,tt4.DQ,tt4.BM,tt4.SS,tt4.ZW,tt4.ZJ,tt4.XM OA_XM,tt4.SJHM OA_SJHM "
		 * ); sql.append(
		 * " FROM  T_ZHFW_TZRXX TEMP                                        ");
		 * sql.append(
		 * " LEFT JOIN v_p2p_user_tjr tt1 on TEMP.USERID = tt1.USER_NAME ");
		 * sql.append(" LEFT JOIN t_zhfw_zqzrlb tt2 on TEMP.ZQID = tt2.ID ");
		 * sql.append(
		 * " LEFT JOIN t_zhfw_user_tgcode TT5 ON TT5.USER_NAME=TEMP.USERID");
		 * sql.append(
		 * " LEFT JOIN t_zhfw_user_tgcode TT6 ON TT6.TGCODE=tt5.TJR_TGCODE");
		 * sql.append(
		 * " LEFT JOIN t_zhfw_user TT7 ON TT7.USER_NAME=TT6.USER_NAME");
		 * sql.append(" LEFT JOIN T_ZHFW_OA_USER TT4 ON"); sql.append(
		 * " case when tt1.TJR is null or tt1.TJR ='' then case when TT7.TJR is null or TT7.TJR ='' then tt1.TJR = TT4.GYH else TT7.TJR = TT4.GYH  end else  tt1.TJR = TT4.GYH end"
		 * ); sql.append(" where 1=1 ");
		 */
		sql.append(
				"select temp4.*,tt4.GYH OA_GYH,tt4.DQ,tt4.BM,tt4.SS,tt4.ZW,tt4.ZJ,tt4.XM OA_XM,tt4.SJHM OA_SJHM from (select temp3.*");
		sql.append(" ,case when temp3.tjr1 is null or temp3.tjr1='' then");
		sql.append(" 	case when temp3.tjr2 is null or temp3.tjr2='' then");
		sql.append(" 		case when temp3.tjr3 is null or temp3.tjr3='' then null");
		sql.append("         else temp3.tjr3 end");
		sql.append("     else temp3.tjr2 end");
		sql.append("  else temp3.tjr1 end TJR");
		sql.append(" ,case when temp3.tjr1 is null or temp3.tjr1='' then");
		sql.append(" 	case when temp3.tjr2 is null or temp3.tjr2='' then");
		sql.append(" 		case when temp3.tjr3 is null or temp3.tjr3='' then null");
		sql.append("         else '03' end");
		sql.append("     else '02' end");
		sql.append("  else '01' end KHDJ");
		sql.append(" from (select temp2.*");
		sql.append(" ,case when temp2.tjr2 is null or temp2.tjr2 = '' then");
		sql.append(" 	(select tu3.tjr from t_zhfw_user_tgcode tg3");
		sql.append(" 		inner join t_zhfw_user_tgcode tg2 on tg3.TJR_TGCODE=tg2.TGCODE");
		sql.append(" 		inner join t_zhfw_user_tgcode tg1 on tg2.TJR_TGCODE=tg1.TGCODE ");
		sql.append("         inner join t_zhfw_user tu3 on tg1.USER_NAME=tu3.USER_NAME");
		sql.append("         where tg3.USER_NAME=temp2.USERID) end tjr3");
		sql.append(
				" from (SELECT TEMP.ID, TEMP.USERID, tt1.SJHM, TEMP.ZQID, TEMP.TCJE, TEMP.TZSJ ,TEMP.TCZT, TEMP.HKZT");
		sql.append(" , TEMP.JXRQ HKRQ, TEMP.TZSY, TEMP.TZ_SOURCE, TEMP.POS_CODE ,datediff(now(),temp.tzsj) IS_JX");
		sql.append(" , TEMP.HTBH,tt2.SJSYL,tt2.SJSYL_SHOW ,tt2.XMMC, tt2.JE, tt2.JD, tt2.QX, tt2.FBSJ, tt2.ZT");
		sql.append(" , tt2.JKR, tt2.DFRID, tt2.MJQ, tt2.JXRQ, tt2.XM_TYPE, tt2.HK_TYPE, tt2.QX_MONTH,tt1.tjr tjr1  ");
		sql.append(" , case when tt1.tjr is null or tt1.tjr = '' then ");
		sql.append(" 	(select tu2.tjr from t_zhfw_user_tgcode tg2");
		sql.append(" 		inner join t_zhfw_user_tgcode tg1 on tg2.TJR_TGCODE=tg1.TGCODE ");
		sql.append("         inner join t_zhfw_user tu2 on tg1.USER_NAME=tu2.USER_NAME");
		sql.append("         where tg2.USER_NAME=tt1.USER_NAME) end tjr2 ");
		sql.append(
				" FROM  T_ZHFW_TZRXX TEMP LEFT JOIN t_zhfw_user tt1 on TEMP.USERID = tt1.USER_NAME and tt1.DISABLED=1 ");
		sql.append(" LEFT JOIN t_zhfw_zqzrlb tt2 on TEMP.ZQID = tt2.ID");
		sql.append(" where 1=1");
		if (StringUtils.isNotEmpty(userId)) {
			sql.append(" AND TEMP.USERID like " + "'%" + userId + "%'");
		}
		if (StringUtils.isNotEmpty(SJHM)) {
			sql.append(" AND tt1.SJHM like '%" + SJHM + "%' ");
		}
		if (StringUtils.isNotEmpty(TZSJ_START)) {
			sql.append(" and TEMP.TZSJ >= :TZSJ_START ");
			parmsMap.put("TZSJ_START", TZSJ_START + " 00:00:00");
		}
		if (StringUtils.isNotEmpty(TZSJ_END)) {
			sql.append(" and TEMP.TZSJ <= :TZSJ_END ");
			parmsMap.put("TZSJ_END", TZSJ_END + " 23:59:59");
		}
		if (StringUtils.isNotEmpty(TCZT) && (TCZT.equals("00"))) {
			sql.append(" and TEMP.TCZT = '成功' ");
		}
		if (StringUtils.isNotEmpty(TCZT) && (TCZT.equals("01"))) {
			sql.append(" and TEMP.TCZT = '待审核' ");
		}
		if (StringUtils.isNotEmpty(TCZT) && (TCZT.equals("02"))) {
			sql.append(" and TEMP.TCZT = '失败' ");
		}
		if (StringUtils.isNotEmpty(HKZT)) {
			sql.append(" and TEMP.HKZT = :HKZT ");
			parmsMap.put("HKZT", HKZT);
		}
		if (StringUtils.isNotEmpty(TZ_SOURCE)) {
			sql.append(" and TEMP.TZ_SOURCE = :TZ_SOURCE ");
			parmsMap.put("TZ_SOURCE", TZ_SOURCE);
		}
		if (StringUtils.isNotEmpty(XMMC)) {
			sql.append(" AND tt2.XMMC like '%" + XMMC + "%' ");
		}
		if (StringUtils.isNotEmpty(Q_HTBH)) {
			sql.append(" AND TEMP.HTBH like '%" + Q_HTBH + "%' ");
		}
		sql.append(") temp2) temp3) temp4 left join T_ZHFW_OA_USER TT4 on TT4.GYH=temp4.TJR");
		sql.append(" where 1=1");
		if (StringUtils.isNotEmpty(TJR)) {
			sql.append(" AND temp4.TJR like " + "'%" + TJR + "%'");
		} else {
			sql.append(" AND temp4.TJR like " + "'" + 150 + "%'");
		}
		/*
		 * if(StringUtils.isNotEmpty(Q_DQ)){ sql.append(" AND tt4.DQ = :DQ ");
		 * parmsMap.put("DQ", Q_DQ); } if(StringUtils.isNotEmpty(Q_BM)){
		 * sql.append(" AND tt4.BM = :BM "); parmsMap.put("BM", Q_BM); }
		 * if(StringUtils.isNotEmpty(Q_SS)){ sql.append(" AND tt4.SS = :SS ");
		 * parmsMap.put("SS", Q_SS); }
		 */
		sql.append(" order by temp4.TZSJ DESC ");
		if (0 != pageSize) {
			sql.append("  limit :curpage,:pageSize ");
			parmsMap.put("curpage", curpage);
			parmsMap.put("pageSize", pageSize);
		}

		logger.info("youjiaGrttzq sql is : " + sql.toString());
		logger.info("youjiaGrttzq parmsMap is : " + parmsMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), parmsMap);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.bringway.p2p.dao.P2PGrtzzqDao#getPageCount(net.sf.json.JSONObject)
	 */
	@Override
	public int getyoujiaPageCount(JSONObject parms) {
		String userId = parms.optString("userId");
		String TZSJ_START = parms.optString("TZSJ_START");
		String TZSJ_END = parms.optString("TZSJ_END");
		String TCZT = parms.optString("TCZT");
		String HKZT = parms.optString("HKZT");
		String SJHM = parms.optString("SJHM");
		String TZ_SOURCE = parms.optString("TZ_SOURCE");
		String XMMC = parms.optString("XMMC");
		String TJR = parms.optString("TJR");
		String Q_HTBH = parms.optString("Q_HTBH");
		String Q_DQ = parms.optString("Q_DQ");
		String Q_BM = parms.optString("Q_BM");
		String Q_SS = parms.optString("Q_SS");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		sql.append(" SELECT 	COUNT(*) 	CT							");
		sql.append(" FROM  T_ZHFW_TZRXX TEMP                            ");
		sql.append(" LEFT JOIN t_zhfw_user tt1 on TEMP.USERID = tt1.USER_NAME and tt1.DISABLED=1  ");
		sql.append(" LEFT JOIN t_zhfw_zqzrlb tt2 on TEMP.ZQID = tt2.ID ");
		/*
		 * sql.append(" LEFT JOIN T_ZHFW_OA_USER TT4 ON tt1.TJR = TT4.GYH ");
		 * sql.append(
		 * " LEFT JOIN t_zhfw_user_tgcode TT5 ON TT5.USER_NAME=TEMP.USERID");
		 * sql.append(
		 * " LEFT JOIN t_zhfw_user_tgcode TT6 ON TT6.TGCODE=tt5.TJR_TGCODE");
		 * sql.append(
		 * " LEFT JOIN t_zhfw_user TT7 ON TT7.USER_NAME=TT6.USER_NAME");
		 */
		sql.append(" where 1=1");
		if (StringUtils.isNotEmpty(TJR)) {
			sql.append(" AND tt1.TJR like " + "'%" + TJR + "%'");
		} else {
			sql.append(" AND tt1.TJR like " + "'" + 150 + "%'");
		}
		if (StringUtils.isNotEmpty(userId)) {
			sql.append(" AND TEMP.USERID like " + "'%" + userId + "%'");
		}
		if (StringUtils.isNotEmpty(SJHM)) {
			sql.append(" AND tt1.SJHM like '%" + SJHM + "%' ");
		}
		if (StringUtils.isNotEmpty(TZSJ_START)) {
			sql.append(" and TEMP.TZSJ >= :TZSJ_START ");
			parmsMap.put("TZSJ_START", TZSJ_START + " 00:00:00");
		}
		if (StringUtils.isNotEmpty(TZSJ_END)) {
			sql.append(" and TEMP.TZSJ <= :TZSJ_END ");
			parmsMap.put("TZSJ_END", TZSJ_END + " 23:59:59");
		}
		if (StringUtils.isNotEmpty(TCZT) && (TCZT.equals("00"))) {
			sql.append(" and TEMP.TCZT = '成功' ");
		}
		if (StringUtils.isNotEmpty(TCZT) && (TCZT.equals("01"))) {
			sql.append(" and TEMP.TCZT = '待审核' ");
		}
		if (StringUtils.isNotEmpty(TCZT) && (TCZT.equals("02"))) {
			sql.append(" and TEMP.TCZT = '失败' ");
		}
		if (StringUtils.isNotEmpty(HKZT)) {
			sql.append(" and TEMP.HKZT = :HKZT ");
			parmsMap.put("HKZT", HKZT);
		}
		if (StringUtils.isNotEmpty(TZ_SOURCE)) {
			sql.append(" and TEMP.TZ_SOURCE = :TZ_SOURCE ");
			parmsMap.put("TZ_SOURCE", TZ_SOURCE);
		}
		if (StringUtils.isNotEmpty(XMMC)) {
			sql.append(" AND tt2.XMMC like '%" + XMMC + "%' ");
		}
		if (StringUtils.isNotEmpty(Q_HTBH)) {
			sql.append(" AND TEMP.HTBH like '%" + Q_HTBH + "%' ");
		}
		/*
		 * if(StringUtils.isNotEmpty(Q_DQ)){ sql.append(" AND tt4.DQ = :DQ ");
		 * parmsMap.put("DQ", Q_DQ); } if(StringUtils.isNotEmpty(Q_BM)){
		 * sql.append(" AND tt4.BM = :BM "); parmsMap.put("BM", Q_BM); }
		 * if(StringUtils.isNotEmpty(Q_SS)){ sql.append(" AND tt4.SS = :SS ");
		 * parmsMap.put("SS", Q_SS); }
		 */
		logger.info("getyoujiaPageCount sql is : " + sql.toString());
		logger.info("getyoujiaPageCount parmsMap is : " + parmsMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), parmsMap, Integer.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public float getyoujiaGrtzJe(JSONObject parms) {
		String userId = parms.optString("userId");
		String TZSJ_START = parms.optString("TZSJ_START");
		String TZSJ_END = parms.optString("TZSJ_END");
		String TCZT = parms.optString("TCZT");
		String HKZT = parms.optString("HKZT");
		String SJHM = parms.optString("SJHM");
		String TZ_SOURCE = parms.optString("TZ_SOURCE");
		String XMMC = parms.optString("XMMC");
		String TJR = parms.optString("TJR");
		String Q_HTBH = parms.optString("Q_HTBH");
		String Q_DQ = parms.optString("Q_DQ");
		String Q_BM = parms.optString("Q_BM");
		String Q_SS = parms.optString("Q_SS");
		int curpage = parms.optInt("curpage");
		int pageSize = parms.optInt("pageSize");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		sql.append(" SELECT SUM(T.TCJE) S_TZJE FROM");
		sql.append(" (SELECT TEMP.TCJE TCJE ");
		sql.append(" FROM  T_ZHFW_TZRXX TEMP ");
		sql.append(" LEFT JOIN t_zhfw_user tt1 on TEMP.USERID = tt1.USER_NAME and tt1.DISABLED=1");
		sql.append(" LEFT JOIN t_zhfw_zqzrlb tt2 on TEMP.ZQID = tt2.ID ");
		/*
		 * sql.append(" LEFT JOIN T_ZHFW_OA_USER TT4 ON tt1.TJR = TT4.GYH ");
		 * sql.append(
		 * " LEFT JOIN t_zhfw_user_tgcode TT5 ON TT5.USER_NAME=TEMP.USERID");
		 * sql.append(
		 * " LEFT JOIN t_zhfw_user_tgcode TT6 ON TT6.TGCODE=tt5.TJR_TGCODE");
		 * sql.append(
		 * " LEFT JOIN t_zhfw_user TT7 ON TT7.USER_NAME=TT6.USER_NAME");
		 */
		sql.append(" where 1=1 ");

		if (StringUtils.isNotEmpty(TJR)) {
			sql.append(" AND tt1.TJR like " + "'%" + TJR + "%'");
		} else {
			sql.append(" AND tt1.TJR like " + "'" + 150 + "%'");
		}
		if (StringUtils.isNotEmpty(userId)) {
			sql.append(" AND TEMP.USERID like " + "'%" + userId + "%'");
		}
		if (StringUtils.isNotEmpty(SJHM)) {
			sql.append(" AND tt1.SJHM like '%" + SJHM + "%' ");
		}
		if (StringUtils.isNotEmpty(TZSJ_START)) {
			sql.append(" and TEMP.TZSJ >= :TZSJ_START ");
			parmsMap.put("TZSJ_START", TZSJ_START + " 00:00:00");
		}
		if (StringUtils.isNotEmpty(TZSJ_END)) {
			sql.append(" and TEMP.TZSJ <= :TZSJ_END ");
			parmsMap.put("TZSJ_END", TZSJ_END + " 23:59:59");
		}
		if (StringUtils.isNotEmpty(TCZT) && (TCZT.equals("00"))) {
			sql.append(" and TEMP.TCZT = '成功' ");
		}
		if (StringUtils.isNotEmpty(TCZT) && (TCZT.equals("01"))) {
			sql.append(" and TEMP.TCZT = '待审核' ");
		}
		if (StringUtils.isNotEmpty(TCZT) && (TCZT.equals("02"))) {
			sql.append(" and TEMP.TCZT = '失败' ");
		}
		if (StringUtils.isNotEmpty(HKZT)) {
			sql.append(" and TEMP.HKZT = :HKZT ");
			parmsMap.put("HKZT", HKZT);
		}
		if (StringUtils.isNotEmpty(TZ_SOURCE)) {
			sql.append(" and TEMP.TZ_SOURCE = :TZ_SOURCE ");
			parmsMap.put("TZ_SOURCE", TZ_SOURCE);
		}
		if (StringUtils.isNotEmpty(XMMC)) {
			sql.append(" AND tt2.XMMC like '%" + XMMC + "%' ");
		}
		if (StringUtils.isNotEmpty(Q_HTBH)) {
			sql.append(" AND TEMP.HTBH like '%" + Q_HTBH + "%' ");
		}
		/*
		 * if(StringUtils.isNotEmpty(Q_DQ)){ sql.append(" AND tt4.DQ = :DQ ");
		 * parmsMap.put("DQ", Q_DQ); } if(StringUtils.isNotEmpty(Q_BM)){
		 * sql.append(" AND tt4.BM = :BM "); parmsMap.put("BM", Q_BM); }
		 * if(StringUtils.isNotEmpty(Q_SS)){ sql.append(" AND tt4.SS = :SS ");
		 * parmsMap.put("SS", Q_SS); }
		 */
		sql.append(" ) T");
		logger.info("getyoujiaGrtzJe sql is : " + sql.toString());
		logger.info("getyoujiaGrtzJe parmsMap is : " + parmsMap.toString());
		return this.getNamedParameterJdbcTemplate().query(sql.toString(), parmsMap, new ResultSetExtractor() {
			@Override
			public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
				float sJe = 0;
				if (rs.next()) {
					sJe = rs.getFloat("S_TZJE");
				}
				return sJe;
			}
		});
	}

	@Override
	public List<Map<String, Object>> queryTzxxByGyhList(String username, Integer start, Integer limit) {
		StringBuffer sql = new StringBuffer();
		sql.append(
				"select t1.ID,t1.USERID,t3.SJHM,t1.ZQID,t2.XMMC,t2.SJSYL,t1.TCJE TZJE,date_format(t1.TZSJ,'%Y-%m-%d') TZSJ");
		sql.append(
				",t1.TCZT TZZT,t1.HKZT,case when t2.XM_TYPE='01' then date_format(adddate(t1.TZSJ, interval 1 day),'%Y-%m-%d')");
		sql.append(" when t2.XM_TYPE='02' then date_format(t2.JXRQ,'%Y-%m-%d') end JXRQ,case when t2.XM_TYPE='01'");
		sql.append(" then date_format(adddate(t1.TZSJ, interval t2.QX_MONTH month),'%Y-%m-%d') when t2.XM_TYPE='02'");
		sql.append(
				" then date_format(adddate(t2.JXRQ, interval t2.QX-1 day),'%Y-%m-%d') end DQRQ,t1.TZSY,t1.TZ_SOURCE");
		sql.append(" ,t1.POS_CODE,t1.HTBH,t1.ZR_STATUS,t2.XM_TYPE,t2.QX_MONTH,case when t2.XM_TYPE='01'");
		sql.append(
				" then datediff(adddate(t1.TZSJ, interval t2.QX_MONTH month),t1.TZSJ) when t2.XM_TYPE='02' then t2.QX end QX");
		sql.append(
				",case when t2.XM_TYPE='01' then datediff(now(),adddate(t1.TZSJ, interval t2.QX_MONTH month)) when t2.XM_TYPE='02'");
		sql.append(" then datediff(now(),adddate(t2.JXRQ, interval t2.QX-1 day)) end IS_DQ");
		sql.append(" from t_zhfw_tzrxx t1 left join t_zhfw_zqzrlb t2 on t2.ID=t1.ZQID left join t_zhfw_user t3");
		sql.append(" on t3.USER_NAME=t1.USERID where 1=1 and t1.USERID=:username");
		sql.append(" order by t1.TZSJ desc");
		Map<String, Object> paramMap = new HashMap<>();
		if (limit > 0) {
			sql.append(" limit :start,:limit");
			paramMap.put("start", start);
			paramMap.put("limit", limit);
		}
		paramMap.put("username", username);
		logger.info("queryTzxxByGyhList sql is : " + sql.toString());
		logger.info("queryTzxxByGyhList parmsMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@SuppressWarnings("deprecation")
	@Override
	public int getTzxxCountByGyh(String username) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) from t_zhfw_tzrxx t1 where t1.USERID=:username");
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("username", username);
		logger.info("getTzxxCountByGyh sql is : " + sql.toString());
		logger.info("getTzxxCountByGyh parmsMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public float getTzxxSumJeByGyh(String username) {
		StringBuffer sql = new StringBuffer();
		sql.append("select sum(t1.TCJE) TZJE from t_zhfw_tzrxx t1  where t1.USERID=:username");
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("username", username);
		logger.info("getTzxxSumJeByGyh sql is : " + sql.toString());
		logger.info("getTzxxSumJeByGyh parmsMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap, new ResultSetExtractor<Float>() {

			@Override
			public Float extractData(ResultSet resultSet) throws SQLException, DataAccessException {
				float result=0;
				while (resultSet.next()) {
					result=resultSet.getFloat("TZJE");
				}
				return result;
			}
		});
	}
}
